<?php
namespace util;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;


/**
 * 解析 excel 文件
 * @param string $filename
 * @return array
 */
function excelParseFile(string $filename): array {
    $reader = new Xlsx();
    $reader->setReadDataOnly(true);

    $spreadsheet = $reader->load($filename);

    $dataArray = [];
    foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
        $title = $sheet->getTitle();
        if (!str_starts_with($title, "DATA")) continue;

        $sheetData = $sheet->toArray();

        $rowNames   = $sheetData[0]; //字段名称
        $rowFields0 = $sheetData[1]; //字段(可能有类型, 如 id:int)

        $rowFields  = [];

        $names  = [];
        $fields = [];
        $types  = [];
        foreach ($rowFields0 as $col => $v) {
            $fieldType = explode(':', $v);
            $field  = $fieldType[0];
            $type = $fieldType[1] ?? null;

            $rowFields[ $col ] = $field;

            $names[ $field ] = $rowNames[ $col ];
            $fields[ $field ]= $field;
            $types[ $field ] = $type;
        }

        $data   = [];
        $data[] = $names;
        $data[] = $fields;
        $data[] = $types;
        foreach ($sheetData as $row => $rs) {
            if ($row == 0 || $row == 1) continue;

            $rowVal = [];
            foreach ($rs as $col => $v) $rowVal[ $rowFields[$col] ] = $v;
            $data[] = $rowVal;
        }

        $dataArray[] = $data;
    }

    return $dataArray;
}
